﻿Public Class frmTratadoraConsulta
    Dim arrayTotales(8) As String

    Private Sub frmConsulta_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        lblTitulo.Text = varTituloTratadora

        'Me.dgvConsulta.ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False

        Me.Text = "TRATADORA - Consulta: " & varTituloTratadora
        lblCriterio1.Visible = True
        lblCriterio1.Text = "GRUPO"
        cboCriterio1.Visible = True
        cboCriterio1.Items.Add("Seleccionar...")
        cboCriterio1.Items.Add("LOTES")
        cboCriterio1.Items.Add("BATCHS")
        cboCriterio1.Items.Add("CALIBRACIONES")
        cboCriterio1.Items.Add("EVENTOS")
        cboCriterio1.Items.Add("ALARMAS")
        cboCriterio1.SelectedIndex = 1
        cboCriterio1.Enabled = False
        lblFechaDesde.Visible = True
        lblFechaDesde.Text = "Desde"
        dtpFechaDesde.Visible = True
        dtpFechaDesde.Value = "01/01/2010"
        lblFechaHasta.Visible = True
        lblFechaHasta.Text = "Hasta"
        dtpFechaHasta.Visible = True
        dtpFechaHasta.Value = Today
        lblCriterio2.Visible = True
        lblCriterio2.Text = "LOTE"
        txtCriterio2.Visible = True
        lblCriterio3.Visible = True
        lblCriterio3.Text = "VARIEDAD"
        txtCriterio3.Visible = True
        lblCriterio4.Visible = True
        lblCriterio4.Text = "RECETA"
        txtCriterio4.Visible = True


        'Ejecuto la busqueda sin que apreten el boton
        cmdBuscar_Click(Nothing, Nothing)

    End Sub


    Private Sub cmdBuscar_Click(sender As Object, e As EventArgs) Handles cmdBuscar.Click

        Try
            Me.Cursor = System.Windows.Forms.Cursors.WaitCursor

            'Limpio
            lblTotal.Text = "Total: $0.00"
            lblTotal.Visible = False
            lblTotal19.Text = "Total: $0.00"
            lblTotal19.Visible = False

            'Valido
            If dtpFechaDesde.Visible = True And dtpFechaHasta.Visible = True And dtpFechaDesde.Value > dtpFechaHasta.Value Then
                MsgBox("La fecha DESDE debe ser menor que la fecha HASTA", MsgBoxStyle.Exclamation + vbOKOnly, "Atención")
                dtpFechaDesde.Focus()
                Exit Sub
            End If

            If cboCriterio1.Text = "Seleccionar..." Then
                MsgBox("Debe seleccionar " & lblCriterio1.Text, MsgBoxStyle.Exclamation + vbOKOnly, "Atención")
                cboCriterio1.Focus()
                Exit Sub
            End If

            'If cboCriterio2.Text = "Seleccionar..." Then
            '    MsgBox("Debe seleccionar " & lblCriterio2.Text, MsgBoxStyle.Exclamation + vbOKOnly, "Atención")
            '    cboCriterio2.Focus()
            '    Exit Sub
            'End If

            'If cboCriterio3.Text = "Seleccionar..." Then
            '    MsgBox("Debe seleccionar " & lblCriterio3.Text, MsgBoxStyle.Exclamation + vbOKOnly, "Atención")
            '    cboCriterio3.Focus()
            '    Exit Sub
            'End If
            '-------------

            dgvConsulta.DataSource = Nothing

            Select Case cboCriterio1.Text

                Case Is = "LOTES"
                    sqlConsulta = "SELECT DATE(Fecha) As Fecha, CONVERT(TIME(Fecha),CHAR) As Hora, Lote, Variedad, Nombre_Receta As Receta, Cantidad_Batch As Batch, Peso_Procesar As ""Procesar (Kg)"", ROUND(Peso_Total) As ""Procesado (Kg)"", ROUND(Consumo_L1/1000,3) As ""L1 (Lts)"" , ROUND(Consumo_L2/1000,3) As ""L2 (Lts)"", ROUND(Consumo_L3/1000,3) As ""L3 (Lts)"", ROUND(Consumo_L4/1000,3) As ""L4 (Lts)"", ROUND(Consumo_L5/1000,3) As ""L5 (Lts)"", ROUND(Consumo_L6/1000,3) As ""L6 (Lts)"", ROUND(Consumo_P1/1000,1) As ""P1 (Kg)"", ROUND(Consumo_P2/1000,1) As ""P2 (Kg)"""
                    sqlConsulta = sqlConsulta & " FROM " & cboCriterio1.Text
                    sqlConsulta = sqlConsulta & " WHERE Tratadora_Id =" & Me.Tag
                    sqlConsulta = sqlConsulta & " AND Fecha BETWEEN '" & Format(dtpFechaDesde.Value, "yyyy-MM-dd") & "' AND '" & Format(dtpFechaHasta.Value, "yyyy-MM-dd 23:59:59") & "'"
                    If txtCriterio2.Text.Trim <> "" Then sqlConsulta = sqlConsulta & " AND Lote LIKE '%" & txtCriterio2.Text & "%'"
                    If txtCriterio3.Text.Trim <> "" Then sqlConsulta = sqlConsulta & " AND Variedad LIKE '%" & txtCriterio3.Text & "%'"
                    If txtCriterio4.Text.Trim <> "" Then sqlConsulta = sqlConsulta & " AND Nombre_Receta LIKE '%" & txtCriterio4.Text & "%'"

                    ''Sumo el Total procesado
                    'sql = "SELECT IFNULL(SUM(peso_total),0) AS Total"
                    'sql = sql & " FROM " & cboCriterio1.Text
                    'sql = sql & " WHERE Tratadora_Id =" & Me.Tag
                    'sql = sql & " AND Fecha BETWEEN '" & Format(dtpFechaDesde.Value, "yyyy-MM-dd") & "' AND '" & Format(dtpFechaHasta.Value, "yyyy-MM-dd 23:59:59") & "'"
                    'If txtCriterio2.Text.Trim <> "" Then sql = sql & " AND Variedad LIKE '%" & txtCriterio2.Text & "%'"
                    'If txtCriterio3.Text.Trim <> "" Then sql = sql & " AND Nombre_Receta LIKE '%" & txtCriterio3.Text & "%'"
                    'Dim cmd As New MySql.Data.MySqlClient.MySqlCommand(sql)
                    'Dim varTotal As Double
                    'varTotal = cn.getScalar(cmd)
                    'If Not IsNothing(varTotal) Then
                    '    lblTotal.Visible = True
                    '    lblTotal.Text = "Total procesado: " & Format(varTotal, "0.00")
                    'End If

                    'Totales
                    sql = "SELECT IFNULL(ROUND(SUM(peso_total)),0) AS Total_peso_procesado, IFNULL(ROUND(SUM(consumo_l1/1000),0),0) AS Total_consumo_l1, IFNULL(ROUND(SUM(consumo_l2/1000),0),0) AS Total_consumo_l2, IFNULL(ROUND(SUM(consumo_l3/1000),0),0) AS Total_consumo_l3, IFNULL(ROUND(SUM(consumo_l4/1000),0),0) AS Total_consumo_l4, IFNULL(ROUND(SUM(consumo_l5/1000),0),0) AS Total_consumo_l5, IFNULL(ROUND(SUM(consumo_l6/1000),0),0) AS Total_consumo_l6, IFNULL(ROUND(SUM(consumo_p1/1000),0),0) AS Total_consumo_p1, IFNULL(ROUND(SUM(consumo_p2/1000),0),0) AS Total_consumo_p2"
                    sql = sql & " FROM " & cboCriterio1.Text
                    sql = sql & " WHERE Tratadora_Id =" & Me.Tag
                    sql = sql & " AND Fecha BETWEEN '" & Format(dtpFechaDesde.Value, "yyyy-MM-dd") & "' AND '" & Format(dtpFechaHasta.Value, "yyyy-MM-dd 23:59:59") & "'"
                    If txtCriterio2.Text.Trim <> "" Then sql = sql & " AND Lote LIKE '%" & txtCriterio2.Text & "%'"
                    If txtCriterio3.Text.Trim <> "" Then sql = sql & " AND Variedad LIKE '%" & txtCriterio3.Text & "%'"
                    If txtCriterio4.Text.Trim <> "" Then sql = sql & " AND Nombre_Receta LIKE '%" & txtCriterio4.Text & "%'"
                    Dim cmd As New MySql.Data.MySqlClient.MySqlCommand(sql)
                    Dim dr As MySql.Data.MySqlClient.MySqlDataReader
                    dr = cn.getResultados(cmd)
                    dr.Read()

                    lblTotal.Visible = True
                    lblTotal.Text = "Procesado (Kg): " & Format(dr("Total_peso_procesado"), "0")
                    arrayTotales(0) = Format(dr("Total_peso_procesado"), "0")

                    lblTotal2.Visible = True
                    lblTotal2.Text = "L1 (Lts): " & Format(dr("Total_consumo_l1"), "0")
                    arrayTotales(1) = Format(dr("Total_consumo_l1"), "0")

                    lblTotal3.Visible = True
                    lblTotal3.Text = "L2 (Lts): " & Format(dr("Total_consumo_l2"), "0")
                    arrayTotales(2) = Format(dr("Total_consumo_l2"), "0")

                    lblTotal4.Visible = True
                    lblTotal4.Text = "L3 (Lts): " & Format(dr("Total_consumo_l3"), "0")
                    arrayTotales(3) = Format(dr("Total_consumo_l3"), "0")

                    lblTotal5.Visible = True
                    lblTotal5.Text = "L4 (Lts): " & Format(dr("Total_consumo_l4"), "0")
                    arrayTotales(4) = Format(dr("Total_consumo_l4"), "0")

                    lblTotal6.Visible = True
                    lblTotal6.Text = "L5 (Lts): " & Format(dr("Total_consumo_l5"), "0")
                    arrayTotales(5) = Format(dr("Total_consumo_l5"), "0")

                    lblTotal7.Visible = True
                    lblTotal7.Text = "L6 (Lts): " & Format(dr("Total_consumo_l6"), "0")
                    arrayTotales(6) = Format(dr("Total_consumo_l6"), "0")

                    lblTotal8.Visible = True
                    lblTotal8.Text = "P1 (Kg): " & Format(dr("Total_consumo_P1"), "0")
                    arrayTotales(7) = Format(dr("Total_consumo_P1"), "0")

                    lblTotal19.Visible = True
                    lblTotal19.Text = "P2 (Kg): " & Format(dr("Total_consumo_P2"), "0")
                    arrayTotales(8) = Format(dr("Total_consumo_P2"), "0")

                    dr.Close()

            End Select

            cn.LlenarGrilla(dgvConsulta, sqlConsulta)

        Catch ex As Exception
            MensajeError()

        Finally
            Me.Cursor = System.Windows.Forms.Cursors.Default
        End Try

    End Sub

    Private Sub cmdExportarAExcel_Click(sender As Object, e As EventArgs) Handles cmdExportarAExcel.Click

        'General.General.ExportarDatosExcel(dgvConsulta, Me.Text & cboCriterio1.Text)

        General.General.ExportarDatosExcelConTotales(dgvConsulta, Me.Text, arrayTotales, "LOTES")
    End Sub

    Private Sub btnLimpiarFiltro_Click(sender As Object, e As EventArgs) Handles btnLimpiarFiltro.Click
        dtpFechaDesde.Value = "01/01/2010"
        dtpFechaHasta.Value = Today
        txtCriterio2.Text = ""
        txtCriterio3.Text = ""
        cmdBuscar_Click(Nothing, Nothing)
    End Sub
End Class